package cn.huugh.dao.impl;

import cn.huugh.dao.IUserDao;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.PropertySource;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.*;

@Repository("UserDao")
@PropertySource(value={"classpath:config.properties"})
public class UserDao implements IUserDao {
    @Value("${jdbc.url}")
    private String url;
    @Value("${jdbc.username}")
    private String userName;
    @Value("${jdbc.password}")
    private String password;
    //private String password="root";
    @Value("${jdbc.driver}")
    private String driver="com.mysql.cj.jdbc.Driver";

    private Connection connection;


    public void connectDataBase(){
        //System.out.println(url);
        try {
            Class dver = Class.forName(driver);
            connection = DriverManager.getConnection(url,userName,password);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }
    private Set<String> thisGetTips(String fragment){
        HashSet<String> resultToReturn = new HashSet<>();
        try {
            PreparedStatement statement = connection.prepareStatement(//首要搜索作品名称
                    "select 作品名称 from competition where 作品名称 like ?;");
            statement.setString(1,"%"+fragment+"%");
            ResultSet resultSet = statement.executeQuery();
            while(resultSet.next()){
                resultToReturn.add(resultSet.getString(1));
            }

            statement = connection.prepareStatement(
                    "select 负责人 from competition where 负责人 like ?;");//次要搜索负责人
            statement.setString(1,"%"+fragment+"%");
            resultSet = statement.executeQuery();
            while(resultSet.next()){
                resultToReturn.add(resultSet.getString(1));
            }

            statement = connection.prepareStatement(//首要搜索作品名称
                    "select 小组名 from competition where 小组名 like ?;");
            statement.setString(1,"%"+fragment+"%");
            resultSet = statement.executeQuery();
            while(resultSet.next()){
                System.out.println(resultSet.getString(1));
                resultToReturn.add(resultSet.getString(1));
            }

            statement = connection.prepareStatement(
                    "select 团队成员 from competition where 团队成员 like ?;");
            statement.setString(1,"%"+fragment+"%");
            resultSet = statement.executeQuery();
            while(resultSet.next()){
                resultToReturn.add(resultSet.getString(1));
            }

            statement = connection.prepareStatement(
                    "select 指导老师 from competition where 指导老师 like ?;");
            statement.setString(1,"%"+fragment+"%");
            resultSet = statement.executeQuery();
            while(resultSet.next()){
                resultToReturn.add(resultSet.getString(1));
            }

            statement = connection.prepareStatement(
                    "select 类型 from competition where 类型 like ?;");
            statement.setString(1,"%"+fragment+"%");
             resultSet = statement.executeQuery();
            if(resultSet.next()) {
                resultToReturn.add(resultSet.getString(1));//类型有大量重复，此举避免浪费资源
            }
            statement = connection.prepareStatement(
                    "select 学院 from competition where 学院 like ?;");
            statement.setString(1,"%"+fragment+"%");
            resultSet = statement.executeQuery();
            if(resultSet.next()) {//学院也有大量重复
                resultToReturn.add(resultSet.getString(1));
            }
            statement = connection.prepareStatement(
                    "select 奖项 from competition where 奖项 like ?;");
            statement.setString(1,"%"+fragment+"%");
            resultSet = statement.executeQuery();
            if(resultSet.next()) {
                resultToReturn.add(resultSet.getString(1));
            }


            return resultToReturn;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return new HashSet<>();
    }
    @Override
    public Set<String> getTips(String fragment){
        fragment=fragment.trim();
        if(((int)fragment.charAt(0) >= 97 && (int)fragment.charAt(0) <= 122) ||
                ((int)fragment.charAt(0) >= 65 && (int)fragment.charAt(0) <= 90)
        ){//首字母要是是英文字母的话，可以直接搜索
            return thisGetTips(fragment);
        }else{
            for (int i = fragment.length()-1; i >= 0; i--) {
                System.out.println(fragment.charAt(i));
                System.out.println(fragment);
                if(((int)fragment.charAt(i) >= 97 && (int)fragment.charAt(i) <= 122)
                ){//小写字母做结尾的话，删掉英文字母进行搜索
                    fragment = fragment.substring(0,fragment.length() - 1);
                }else{
                    break;
                }
            }
            return thisGetTips(fragment);
        }
    }

    @Override
    public HashMap<String,String> buildTips(Set tipSet) {
        HashMap<String,String> map = new HashMap<>();
        int i=0;
        for (Object o : tipSet) {
            map.put("t"+i,(String) o);
                    i++;
        }
        while(i<10){
            map.put("t"+i, "");
            i++;
        }
        return map;
    }

    @Override
    public List<?> getTable(String fragment) {
        try {
            PreparedStatement statement = connection.prepareStatement(
                    "select * from competition where 类型 like ? or 学院 like ?  or 作品名称 like ? or 作品类型 like ? or 负责人 like ? or 团队成员 like ? or 奖项 like ? or 指导老师 like ? or 小组名 like ?;");
            for (int i = 1; i < 10; i++) {
                statement.setString(i,"%"+fragment+"%");
            }
            //statement.executeQuery();
            ResultSet result = statement.executeQuery();
            List<HashMap> allResult = new ArrayList<>();
            while(result.next()) {
                HashMap<String,String> resultMap = new HashMap<>();
                resultMap.put("type", result.getString(1));
                resultMap.put("college", result.getString(2));
                resultMap.put("workName", result.getString(3));
                resultMap.put("workType", result.getString(4));
                resultMap.put("leader", result.getString(5));
                resultMap.put("members", result.getString(6));
                resultMap.put("prize", result.getString(7));
                resultMap.put("teacher", result.getString(8));
                resultMap.put("teamName", result.getString(9));
                allResult.add(resultMap);
            }
            return allResult;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return new ArrayList<>();//防空指针
    }
}
